﻿using Kingdee.BOS;
using Kingdee.BOS.App.Data;
using Kingdee.BOS.Contracts;
using Kingdee.BOS.Core;
using Kingdee.BOS.Util;
using System;
using System.ComponentModel;
using System.Data;


namespace LiMinCRMPlugin
{
    /// <summary>
    /// 
    /// </summary>
    /// <remarks>
    /// 功能：
    /// 创建人：wzx
    /// 创建日期：2024/4/13 
    /// </remarks>
    /// 
    [Description("自动执行计划批量更新销售订单预计发货时间")]
    [HotUpdate]
    public class SOExpectedDeliveryDate : IScheduleService
    {
        public void Run(Context ctx, Schedule schedule)
        {
            //即时库存表
            //CREATE TABLE InventoryTable
            //(
            //    Id varchar(50) PRIMARY KEY,
            //    FMaterialId INT,
            //    FStockOrgId INT,
            //    FBaseQty decimal
            //);

            //生产订单库存表
            //CREATE TABLE POInventoryTable
            //(
            //    Id varchar(50) PRIMARY KEY,
            //    FMaterialId INT,
            //    FStockOrgId INT,
            //    FBaseQty decimal,
            //    FPlanFinishDate DateTime
            //);
            #region 所有未完工的生产订单信息插入自定义生产订单库存表
            string sql_PO = string.Format(@"/*dialect*/select b.FENTRYID,(FBaseNoStockInQty)RemainingQTY,FPlanFinishDate,FStockInOrgId,FMaterialId from T_PRD_MO a
				inner join T_PRD_MOENTRY b on a.FID=b.FID
				inner join T_PRD_MOENTRY_A c on b.FENTRYID=c.FENTRYID
				inner join T_PRD_MOENTRY_Q d on b.FENTRYID=d.FENTRYID
				where FDOCUMENTSTATUS='C' and FStatus='4' 
                order by FPlanFinishDate");
            DataTable dt_PO = DBUtils.ExecuteDataSet(ctx, sql_PO).Tables[0];
            if (dt_PO.Rows.Count>0)
            {
                foreach (DataRow PO in dt_PO.Rows)
                {
                    string sql_Insert = string.Format(@"/*dialect*/insert into POInventoryTable(Id,FMaterialId,FStockOrgId,FBaseQty,FPlanFinishDate)
		   values('{0}','{1}','{2}','{3}','{4}')", Convert.ToString(PO["FENTRYID"]), Convert.ToDecimal(PO["FMaterialId"]), Convert.ToDecimal(PO["FStockInOrgId"]), Convert.ToDecimal(PO["RemainingQTY"]), Convert.ToString(PO["FPlanFinishDate"]));
                    DBUtils.Execute(ctx, sql_Insert);
                }
               
            }
            
            #endregion


            //销售订单
            string sql_SO = string.Format(@"select b.FENTRYID,FSaleOrgId,FMaterialId,(FBaseUnitQty-FNOTICEBASEQTY)SaleQty  from T_SAL_ORDER a 
		inner join T_SAL_ORDERENTRY b on a.FID=b.FID
		inner join T_SAL_ORDERENTRYDELIPLAN c on b.FENTRYID=c.FENTRYID
		where FDOCUMENTSTATUS='C' and FCloseStatus='A'
        order by FDate");
            DataTable dt_SO = DBUtils.ExecuteDataSet(ctx, sql_SO).Tables[0];
          
            if (dt_SO.Rows.Count > 0)
            {
                foreach (DataRow SO in dt_SO.Rows)
                {
                    int SaleOrFE=0;//声明变量存储临时数据
                    int SaleOrgId = 0;//销售组织
                    int MaterialId = 0; //物料唯一内码
                    int MASTERID = 0;//物料非唯一内码
                    decimal Qty=0;  //即时库存数量(基本单位)
                    decimal ReceivedQty=0;  //生产订单已入库数量(基本单位)
                    decimal SaleQty = 0; //销售订单数量(剩余未通知数量（基本）)
                    string FID = "";
                    DateTime PlanFinishDate; //计划完工日期
                    SaleOrFE = Convert.ToInt32(SO["FENTRYID"]);
                    SaleOrgId = Convert.ToInt32(SO["FSaleOrgId"]);
                    MaterialId = Convert.ToInt32(SO["FMaterialId"]);
                    SaleQty = Convert.ToDecimal(SO["SaleQty"]);
                    string sql_wl = string.Format(@"select FMASTERID from T_BD_MATERIAL where FMATERIALID='{0}'", MaterialId);
                    DataTable dt_wl = DBUtils.ExecuteDataSet(ctx, sql_wl).Tables[0];
                    MASTERID = Convert.ToInt32(dt_wl.Rows[0]["FMASTERID"]);
                    #region 及时库存                  
                    //第一次循环，即时库存临时表为空
                    if (dt_SO.Rows.IndexOf(SO) == 0)
                    {
                        string sql_Inventory = string.Format(@"/*dialect*/select FMaterialId,FStockOrgId,sum(FBaseQty)FBaseQty from T_STK_INVENTORY where FMaterialId='{0}' and FStockOrgId='{1}'  group by FMaterialId,FStockOrgId", MASTERID, SaleOrgId);
                        DataTable dt_Inventory = DBUtils.ExecuteDataSet(ctx, sql_Inventory).Tables[0];
                        if (dt_Inventory != null && dt_Inventory.Rows.Count > 0)
                        {
                            Qty = Convert.ToDecimal(dt_Inventory.Rows[0]["FBaseQty"]);
                            string sql_Insert = string.Format(@"/*dialect*/insert into InventoryTable(Id,FMaterialId,FStockOrgId,FBaseQty)
		   values('{0}','{1}','{2}','{3}')", dt_SO.Rows.IndexOf(SO), MaterialId, SaleOrgId, Qty);
                            DBUtils.Execute(ctx, sql_Insert);
                        }                      
                    }
                    else
                    {
                        //先查询临时表
                        string sql_InventoryTB = string.Format(@"/*dialect*/select ID,FMaterialId,FStockOrgId,FBaseQty from InventoryTable where FMaterialId='{0}' and FStockOrgId='{1}'", MaterialId, SaleOrgId);
                        DataTable dt_InventoryTB = DBUtils.ExecuteDataSet(ctx, sql_InventoryTB).Tables[0];
                        if (dt_InventoryTB != null && dt_InventoryTB.Rows.Count > 0)
                        {
                            Qty = Convert.ToDecimal(dt_InventoryTB.Rows[0]["FBaseQty"]);
                        }
                        //临时表不存在再查系统即时库存
                        else
                        {
                            string sql_Inventory = string.Format(@"/*dialect*/select FMaterialId,FStockOrgId,sum(FBaseQty)FBaseQty from T_STK_INVENTORY where FMaterialId='{0}' and FStockOrgId='{1}' group by FMaterialId,FStockOrgId", MASTERID, SaleOrgId);
                            DataTable dt_Inventory = DBUtils.ExecuteDataSet(ctx, sql_Inventory).Tables[0];
                            if (dt_Inventory != null && dt_Inventory.Rows.Count > 0)
                            {
                                Qty = Convert.ToDecimal(dt_Inventory.Rows[0]["FBaseQty"]);
                                string sql_Insert = string.Format(@"/*dialect*/insert into InventoryTable(Id,FMaterialId,FStockOrgId,FBaseQty)
		   values('{0}','{1}','{2}','{3}')", dt_SO.Rows.IndexOf(SO), MaterialId, SaleOrgId, Qty);
                                DBUtils.Execute(ctx, sql_Insert);
                            }
                        }
                        
                    }
                    #endregion
                    //即时库存足够订单发货，则按当前日期赋值预计发货日期，并扣减自定义即时库存表中库存量
                    if (Qty>= SaleQty)
                    {
                        string sql_UP = string.Format(@"/*dialect*/update T_SAL_ORDERENTRY set F_RDLV_AntiShipmentDate='{0}' where FENTRYID='{1}'",DateTime.Now, SaleOrFE);
                        DBUtils.Execute(ctx, sql_UP);
                        string sql_UPTB = string.Format(@"/*dialect*/update InventoryTable set FBaseQty=FBaseQty-{0} where FMaterialId='{1}' and FStockOrgId='{2}'", SaleQty, MaterialId, SaleOrgId);
                        DBUtils.Execute(ctx, sql_UPTB);
                    }
                    else
                    {
                        #region 查询自定义生产订单临时表
                        string sql_POInventory = string.Format(@"/*dialect*/select ID,FBaseQty,FPlanFinishDate,FStockOrgId,FMaterialId 
                from POInventoryTable
				where FStockOrgId='{0}' and FMaterialId='{1}'
                order by FPlanFinishDate", SaleOrgId, MaterialId);
                        DataTable dt_POInventory = DBUtils.ExecuteDataSet(ctx, sql_POInventory).Tables[0];
                        decimal RemainingQTY = 0;
                        string POInventoryFENTRYID = "";
                        if (dt_POInventory != null && dt_POInventory.Rows.Count > 0)
                        {
                            foreach (DataRow item in dt_POInventory.Rows)
                            {
                                //叠加当前生产订单明细的未入库数量直到足够出库
                                RemainingQTY = RemainingQTY + Convert.ToDecimal(item["FBaseQty"]);
                                PlanFinishDate = Convert.ToDateTime(item["FPlanFinishDate"]);
                                POInventoryFENTRYID = Convert.ToString(item["ID"]);
                                //累加至本条生产订单明细，即时库存+生产订单累计未入库数量足够发货，以生产订单计划完工日期赋值预计发货日期，扣减自定义即时库存表和生产订单库存表
                                if (RemainingQTY+ Qty>= SaleQty)
                                {
                                    //更新销售订单
                                    string sql_UP = string.Format(@"/*dialect*/update T_SAL_ORDERENTRY set F_RDLV_AntiShipmentDate='{0}' where FENTRYID='{1}'", PlanFinishDate, SaleOrFE);
                                    DBUtils.Execute(ctx, sql_UP);
                                    //更新自定义库存
                                    string sql_UPTB = string.Format(@"/*dialect*/update InventoryTable set FBaseQty=0 where FMaterialId='{0}' and FStockOrgId='{1}'", MaterialId, SaleOrgId);
                                    DBUtils.Execute(ctx, sql_UPTB);
                                    //清空预计完工日期小于当前明细对应预计完工日期的生产订单库存量
                                    string sql_UPPOtb = string.Format(@"/*dialect*/update POInventoryTable set FBaseQty=0 where  FMaterialId='{0}' and FStockOrgId='{1}' and FPlanFinishDate<='{2}' and ID<>'{3}'", MaterialId, SaleOrgId, PlanFinishDate, POInventoryFENTRYID);
                                    DBUtils.Execute(ctx, sql_UPPOtb);
                                    //扣减当前明细对应的生产订单库存表
                                    string sql_UPPOCuTB = string.Format(@"/*dialect*/update POInventoryTable set FBaseQty=FBaseQty-{0} where ID='{1}'", SaleQty- Qty , POInventoryFENTRYID);
                                    DBUtils.Execute(ctx, sql_UPPOCuTB);
                                    break;
                                }
                            }

                        }
                        #endregion
                    }


                }
            }

            string sql_DROP = string.Format(@"delete InventoryTable");
            DBUtils.Execute(ctx, sql_DROP);
            string sql_DROP1 = string.Format(@"delete POInventoryTable");
            DBUtils.Execute(ctx, sql_DROP1);
        }

    }
}
